﻿USE [QLLVANTAI]
GO

/****** Object:  StoredProcedure [dbo].[sp_XemLichVanChuen_theothoigian]    Script Date: 09/27/2013 23:02:52 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE proc [dbo].[sp_XemLichVanChuen_theothoigian]
		@ngaydi datetime,  @ngayden datetime, @error nvarchar(100) out, @slxe int out
		
		
as
begin tran


	
	-- kiem tra xem co ton tai lich di theo thoi gian nay ko  
	
	if exists (select * from LichVanChuyen where Gio_Di >= @ngaydi and Gio_Den <= @ngayden and Gio_Di > GETDATE())
	begin
	
		-- set sl xe
		 
		set @slxe = (select COUNT(*)
			from ChiTietLich CTL, LichVanChuyen LVC
			where CTL.Ma_VanChuyen = LVC.Ma_VanChuyen and LVC.Gio_Di >= @ngaydi and LVC.Gio_Den <= @ngayden and LVC.Gio_Di > GETDATE())
		
		
		waitfor delay '00:00:10'			
		-- select data
		
		select LVC.Ma_VanChuyen, Diem_XuatPhat,Diem_Den ,CTL.Ma_Xe, Khoang_Cach,Gio_Di, Gio_Den , CTL.So_TaiXeTrong, CTL.So_PhuLaiTrong  
			from ChiTietLich CTL, LichVanChuyen LVC, TuyenDuong TD
			where CTL.Ma_VanChuyen = LVC.Ma_VanChuyen and LVC.Gio_Di >= @ngaydi and LVC.Gio_Den <= @ngayden and LVC.Ma_TuyenDuong = TD.Ma_TuyenDuong and LVC.Gio_Di > GETDATE()
					
			order by Diem_XuatPhat
			
			commit tran
	end

	else
	begin
		set @error = N'Lỗi: không tồn tại lịch đi trong khoảng thời gian này'
		rollback
	end
GO

